package com.zhi.dao;

import com.zhi.entity.Student;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;

/**
 * 学生对象dao层,与数据库交互
 */
public interface StudentRepository extends JpaRepository<Student, String>{


	@Query("SELECT a.id,a.username,a.password,a.userType FROM User a")
	public List<Object[]> getEntityListWithCodeName();

	/**
	 * 普通分页查询
	 * @param pageable
	 * @return
	 */
	@Query(value = "SELECT s.student_id,s.name,s.id_card_no," +
			"(select CONCAT((select c.faculty_name from faculty c where c.faculty_id=b.faculty_id),'--',b.grade_name) from grade b where b.grade_id=s.grade_id) grade_id," +
			"s.email FROM Student s",
			countQuery ="select count(*) from Student",
			nativeQuery = true)
	public Page<Student> getEntityListWithCodeName(Pageable pageable);

	/**
	 * 分页查询，班级id作为条件
	 * @param pageable
	 * @return
	 */
	@Query(value = "SELECT s.student_id,s.name,s.id_card_no," +
			"(select b.grade_name from grade b where b.grade_id=s.grade_id) grade_id," +
			"s.email FROM Student s where s.grade_Id in ?1 ",
			countQuery ="select count(*) from Student s where s.grade_Id in ?1",
			nativeQuery = true)
	public Page<Student> getEntityListWithCodeName(Pageable pageable,List<String> gradeIds);

}
